High Volume SQLs

In many respects, high volume SQLs are easier to tune than low volume SQLs because the solution is always the same:

• Avoid reading data you don't need.
• Avoid reading the same block twice (or more).
• Avoid indexes.
• Avoid Nested Loop joins.

In practice it is more complex. Although the goal is conceptually simple, it can sometimes be hard to achieve.

The first step is to run your SQL through Explain Plan. Check the execution plan for the following:

• Index scans: unique, range, full, or fast full.
• Nested Loop joins or Nested sub-queries.

Did the above steps help? If not, then your SQL should contain only Full Scans and Merge or Hash joins (plus Index scans and Nested Loop joins that are described in the links above as acceptable for High Volume SQL). If performance is still unacceptable, check the following?

• Are you querying very large partitioned tables?
• Are you joining huge tables to two or more small tables?
• Does the same table appear more than once in the execution plan?
• Does the keyword SORT appear in the execution plan?
• Are you selecting from a view or nested query?
• Are you joining to a small percentage or rows in a large table?

Still want more? If you still have Nested Loops and Indexed table accesses, then either go back and try again or start redesigning your schema.

Otherwise, you should have a nice Full Table Scanning, Hash or Merge Joining query. If you still want to squeeze better performance out of it, then you are going to have to reduce IO. This means reading fewer blocks, which is going to be hard because if you have applied all of the principles above then you probably have little or no redundancy in your query (blocks being read twice or more). There are a couple of things you could do:

• Use indexes to avoid reading a table. You don't store useless columns in indexes, so you can fit more rows per block than the corresponding table. Fewer blocks means less IO.

• All columns already in an index? Try a Fast Full Scan of the index using the INDEX_FFS hint.
• All columns already indexed, but in different indexes? Try an Index Join.
• In desperation, you could create a new index over just the columns you are selecting. Or add columns to an existing index. Speak to your DBA first.
• Depending on the other processes using the table, you could convert it to an Index Organised table and store unwanted column in the overflow segment. This is a pretty big step, talk to your DBA.

• You can reduce IO by discarding fewer rows from a Full Table Scan. If you have a Full Table Scan, and a WHERE predicate is discarding some of the rows, then you may be able to avoid reading those rows by partitioning the table and using a Partition Prune to ignore non-matching rows. Speak to your DBA.

• If your SQL contains aggregation or joins, consider pre-calculating the summary / join in a Materialized View.

• Two or more tables joined with equals joins would join faster is they were clustered on the join key. Speak to your DBA.

• Are you joining rows in Table A to matching rows in Table B, and using full table scans and hash joins? Are both tables big, and you end up not using most of the rows from Table B. If you were to Hash Cluster Table B on the join key, then the Hash Cluster Access would give you all the advantages of a Hash Join without having to read the non-matching rows. Speak to your DBA.